package jw3.c10.controllner;

import jw3.c10.model.Goods;
import jw3.c10.model.Order;
import jw3.c10.utils.DBConnection;
import jw3.c10.utils.QueryByClass;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

public class OrderController {
    public static boolean add(Order o){
        String sql="insert `order` values(0,?,?,?,now())";
        return DBConnection.zsg(sql,
                o.getBarCode(),o.getUserID(),o.getAmount());
    }
    public static int getJNum(int BarCode){
        String sql="select GoodsPrice from goods where BarCode";
        QueryByClass<Goods> qc=new QueryByClass<Goods>();
        return qc.select(Goods.class,sql,BarCode).get(0).getBarCode();
    }
    //通过用户编号查询其消费记录
    public static Vector<Vector<Object>> getDataById(int id){
        String sql = "select OrderID a,GoodsName b,GoodsPrice c,GoodsNum d,orderTime e " +
                "from `order` a join goods b " +
                "on a.BarCode=b.BarCode " +
                "join userinfo c on a.userID=c.id " +
                "where userID=?";
        try {
            Connection conn=DBConnection.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs=ps.executeQuery();
            Vector<Vector<Object>> data = new Vector<Vector<Object>>();
            while (rs.next()){
                //一条数据
                Vector<Object> data1 = new Vector<Object>();

                data1.add(rs.getInt("a"));
                data1.add(rs.getString("b"));
                data1.add(rs.getDouble("c"));
                data1.add(rs.getInt("d"));
                data1.add(rs.getString("e"));
                data1.add(rs.getDouble("c")*rs.getInt("d"));
                //4、将每一条数据放入data中
                data.add(data1);
            }
            return data;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    //传入用户编号->得到积分
    public static long getScoreByID(int id){
        String sql="select sum(GoodsPrice*Amount) a " +
                "from `order` a join goods b " +
                "on a.BarCode=b.BarCode " +
                "join userinfo c on a.userID=c.id " +
                "where userID=? GROUP BY id";
        try {
            Connection conn=DBConnection.getConnection();
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,id);
            ResultSet rs=ps.executeQuery();

            double a=0;
            while (rs.next()){
                a=rs.getDouble("a");
            }
            return Math.round(a);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }
}